package com.myxy.mysqlilt;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.DatabaseErrorHandler;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

import androidx.annotation.Nullable;

import java.net.IDN;
import java.util.ArrayList;
import java.util.List;

public class MyHelper extends SQLiteOpenHelper {

    private static final String TABLE_NAME = "stu_info";

    private static final String _ID = "_id";

    public static final String STU_NAME = "stu_name";

    public static final String STU_GENDER = "stu_gender";

    public static final String STU_MAJOR = "stu_major";

    public static final String STU_GRADUATED = "stu_graduated";

    public List<StuInfoModel> result;

    public MyHelper(@Nullable Context context, @Nullable String name, @Nullable SQLiteDatabase.CursorFactory factory, int version) {
        super(context, name, factory, version);
    }


    /**
     * 创建数据库时的回调方法  当且仅当数据库创建时调用，之后不会再重复调用
     * @param db 数据库访问对象
     */
    @Override
    public void onCreate(SQLiteDatabase db) {
        //学生信息表：stu_info
        //_id，姓名，性别，专业，毕业时间
        db.execSQL("create table stu_info(_id integer primary key autoincrement," +
                "stu_name text not null,stu_gender text not null," +
                "stu_major text,stu_graduated text)");
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

    }

    public void insertStu(String name,String gender,String major,String time){

        //获取数据库的访问对象
        getWritableDatabase().execSQL("insert into "+TABLE_NAME+"("
                +STU_NAME+","
                +STU_GENDER+","
                +STU_MAJOR+","
                +STU_GRADUATED+")values("
                +"'"+name+"'"+","+"'"+gender+"'"+","+"'"+major+"'"+","+"'"+time+"'"+")");

    }

    /**
     * 调用SQLiteDatabase工具类的插入方法插入数据
     */
    public boolean insertStu2(ContentValues values){

        //insert()会返回插入成功后的数据字段ID(即主键自增ID)，如果插入失败则返回-1
        return getWritableDatabase().insert(TABLE_NAME,null,values) != -1;
    }

    /**
     * 调用SQLiteDatabase工具类的删除方法删除数据
     */
    public boolean deleteStuByID(String id){

        //delete from stu_info where _id = 2 and stu_gender = '女'
        //参数2：带占位符的where语句，如 _id=? or stu_gender=?
        //参数3：占位符的具体值，String[]{"2","女"}
        //delete()方法的返回值为首映的数据条目，被删除的数据条目个数
       return getWritableDatabase().delete(TABLE_NAME,"_id=?",new String []{id}) > 0;
    }

    /**
     * 调用SQLiteDatabase工具类的修改方法修改数据
     */
    public boolean update(String graduatedTime,String id){

        ContentValues values = new ContentValues();
        values.put(STU_GRADUATED,graduatedTime);

        //返回值为受影响的记录个数，既被更新成功的记录个数
        return getWritableDatabase().update(
                TABLE_NAME,values,_ID + "=?",new String[]{id}) > 0;
    }


    /**
     * 调用SQLiteDatabase工具类的查询方法查询数据
     */
    public void queryAll(String date){
         result = new ArrayList<>();
        //参数1：带占位符的SQL语句
        //参数2：占位符对应的值
       /*Cursor cursor =  getWritableDatabase().rawQuery("select * from " +
                TABLE_NAME + " where " + STU_GRADUATED + "=?",new String[]{date});*/

       Cursor cursor = getWritableDatabase().query(TABLE_NAME,null,
               STU_GRADUATED + "=? or " + STU_GRADUATED + "=?",
               new String[]{date},null,null,null);


        //判断结果集是否为空
        if(cursor.getCount() > 0){

            cursor.moveToFirst();

            do{
                StuInfoModel model = new StuInfoModel();

                String name = cursor.getString(cursor.getColumnIndexOrThrow(STU_NAME));
                String gender = cursor.getString(cursor.getColumnIndexOrThrow(STU_GENDER));
                String major = cursor.getString(cursor.getColumnIndexOrThrow(STU_MAJOR));
                String graduatedTime = cursor.getString(cursor.getColumnIndexOrThrow(STU_GRADUATED));

                model.setStuName(name);
                model.setStuGender(gender);
                model.setStuMajor(major);
                model.setStuGraduatedTime(graduatedTime);

                result.add(model);

            }while (cursor.moveToNext());
        }
    }


    public void updateStu(String name ,int id ){
        //update 表名 set 列名 =修改的值 where 列名 =列值；
        getWritableDatabase().execSQL("update " +TABLE_NAME
                +" set "+
                STU_NAME +" = " +
                "'"+ name +"'"
                + " where " +
                _ID +" = " +id);
    }
    public void deleteStu(int id){
        getWritableDatabase().execSQL("delete from "+TABLE_NAME+" where " + _ID + "=" + id);
    }




}
